-- 创建通知状态表
CREATE TABLE notification_status (
    id INT IDENTITY(1,1) PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    user_role VARCHAR(20) NOT NULL, -- 'customer' 或 'customer_service'
    notification_type VARCHAR(20) NOT NULL, -- 'new_message', 'conversation_started' 等
    related_id VARCHAR(50), -- 相关的conversation_id或message_id
    message_content NVARCHAR(500), -- 消息内容预览
    sender_name NVARCHAR(100), -- 发送者姓名
    is_read BIT DEFAULT 0, -- 是否已读
    created_time DATETIME DEFAULT GETDATE(),
    updated_time DATETIME DEFAULT GETDATE()
);

-- 创建索引提高查询性能
CREATE INDEX IX_notification_status_user_id ON notification_status(user_id);
CREATE INDEX IX_notification_status_is_read ON notification_status(is_read);
CREATE INDEX IX_notification_status_created_time ON notification_status(created_time);

-- 创建触发器：当console表插入新消息时，自动创建通知
CREATE TRIGGER TR_Console_Insert_Notification
ON console
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @conversation_id VARCHAR(50);
    DECLARE @sender_id VARCHAR(50);
    DECLARE @content NVARCHAR(MAX);
    DECLARE @customer_id VARCHAR(50);
    DECLARE @customer_service_id VARCHAR(50);
    DECLARE @sender_name NVARCHAR(100);
    DECLARE @sender_role VARCHAR(20);
    
    -- 获取插入的消息信息
    SELECT 
        @conversation_id = i.conversation_id,
        @sender_id = i.sender_id,
        @content = i.content
    FROM inserted i;
    
    -- 获取对话信息
    SELECT 
        @customer_id = customer_id,
        @customer_service_id = customer_service_id
    FROM conversation 
    WHERE conversation_id = @conversation_id;
    
    -- 获取发送者信息
    SELECT 
        @sender_name = user_name,
        @sender_role = role
    FROM t_user 
    WHERE user_id = @sender_id;
    
    -- 如果是客户发送的消息，通知客服
    IF @sender_role = 'customer'
    BEGIN
        INSERT INTO notification_status (
            user_id, 
            user_role, 
            notification_type, 
            related_id, 
            message_content, 
            sender_name,
            is_read
        )
        VALUES (
            @customer_service_id,
            'customer_service',
            'new_message',
            @conversation_id,
            LEFT(@content, 100), -- 只保存前100个字符作为预览
            @sender_name,
            0
        );
    END
    
    -- 如果是客服发送的消息，通知客户
    IF @sender_role = 'customer_service'
    BEGIN
        INSERT INTO notification_status (
            user_id, 
            user_role, 
            notification_type, 
            related_id, 
            message_content, 
            sender_name,
            is_read
        )
        VALUES (
            @customer_id,
            'customer',
            'new_message',
            @conversation_id,
            LEFT(@content, 100),
            @sender_name,
            0
        );
    END
END;

-- 创建触发器：当conversation表有新对话时，通知相关客服
CREATE TRIGGER TR_Conversation_Insert_Notification
ON conversation
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @conversation_id VARCHAR(50);
    DECLARE @customer_id VARCHAR(50);
    DECLARE @customer_service_id VARCHAR(50);
    DECLARE @customer_name NVARCHAR(100);
    
    -- 获取插入的对话信息
    SELECT 
        @conversation_id = i.conversation_id,
        @customer_id = i.customer_id,
        @customer_service_id = i.customer_service_id
    FROM inserted i;
    
    -- 获取客户姓名
    SELECT @customer_name = user_name 
    FROM t_user 
    WHERE user_id = @customer_id;
    
    -- 通知客服有新对话
    INSERT INTO notification_status (
        user_id, 
        user_role, 
        notification_type, 
        related_id, 
        message_content, 
        sender_name,
        is_read
    )
    VALUES (
        @customer_service_id,
        'customer_service',
        'conversation_started',
        @conversation_id,
        '发起了新的客服咨询',
        @customer_name,
        0
    );
END;

-- 创建存储过程：标记通知为已读
CREATE PROCEDURE SP_MarkNotificationsAsRead
    @user_id VARCHAR(50),
    @notification_type VARCHAR(20) = NULL,
    @related_id VARCHAR(50) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE notification_status 
    SET is_read = 1, updated_time = GETDATE()
    WHERE user_id = @user_id
        AND is_read = 0
        AND (@notification_type IS NULL OR notification_type = @notification_type)
        AND (@related_id IS NULL OR related_id = @related_id);
END;

-- 创建存储过程：获取用户未读通知
CREATE PROCEDURE SP_GetUnreadNotifications
    @user_id VARCHAR(50),
    @limit INT = 10
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT TOP (@limit)
        id,
        notification_type,
        related_id,
        message_content,
        sender_name,
        created_time
    FROM notification_status
    WHERE user_id = @user_id 
        AND is_read = 0
    ORDER BY created_time DESC;
END;

-- 创建存储过程：清理旧通知（保留最近7天）
CREATE PROCEDURE SP_CleanOldNotifications
AS
BEGIN
    SET NOCOUNT ON;
    
    DELETE FROM notification_status 
    WHERE created_time < DATEADD(DAY, -7, GETDATE());
END; 